var express = require('express');
var Msdb = require("../../../db/Mysqldb.js");
const multer = require('multer');
const path = require('path');
var router = express.Router();
const fs = require('fs').promises; // 使用fs.promises支持异步操作
const fsSync = require('fs'); // 保留同步方法用于初始化
const crypto = require('crypto');

// 确保afterSales目录存在
const salesDir = path.join(__dirname, '../../../../afterSales/');
if (!fsSync.existsSync(salesDir)) {
    fsSync.mkdirSync(salesDir, { recursive: true })
}

// 配置multer存储
const storage = multer.diskStorage({
    destination: (_req, _file, cb) => {
        cb(null, salesDir);
    },
    filename: (req, file, cb) => {
        const timeStp = Date.now();
        const ext = path.extname(file.originalname);
        // 生成32位随机字符串
        const randomStr = crypto.randomBytes(16).toString('hex');
        const filename = `${req.auth.user_id||req.auth.user_id}_${timeStp}_${randomStr}${ext}`
        cb(null, filename);
    }
});

const upload = multer({ storage });

var db = new Msdb();
var getTime = () => {
    return new Date(new Date().getTime() + 8 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');
}

// 辅助函数：删除上传的文件
const deleteUploadedFiles = async (files) => {
    if (!files) return;
    
    for (const field in files) {
        for (const file of files[field]) {
            const filePath = path.join(salesDir, file.filename);
            try {
                await fs.access(filePath);
                await fs.unlink(filePath);
                console.log(`已删除文件: ${filePath}`);
            } catch (err) {
                console.error(`删除文件失败: ${filePath}`, err);
            }
        }
    }
};

/**
 * 生成唯一售后服务单号
 * 格式：上传者ID-年月日-毫秒时间戳-随机字符串
 * 示例：1001-20250607-1717777777777-8a3B9x7z2p5q
 */
function generateServiceOrderId(uploaderId) {
    // 验证上传者ID有效性
    if (!uploaderId || typeof uploaderId !== 'string' && typeof uploaderId !== 'number') {
        throw new Error('无效的上传者ID');
    }
    
    // 获取当前日期时间
    const now = new Date();
    
    // 格式化年月日（20250607）
    const dateStr = now.getFullYear().toString() +
                   String(now.getMonth() + 1).padStart(2, '0') +
                   String(now.getDate()).padStart(2, '0');
                   
    // 获取毫秒级时间戳
    const timestamp = now.getTime();
    
    // 生成12位随机字符（包含大小写字母和数字）
    const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
    let randomStr = '';
    for (let i = 0; i < 12; i++) {
        // 使用crypto模块生成更安全的随机数（Node.js环境）
        const randomIndex = Math.floor(crypto.randomBytes(1)[0] / 256 * chars.length);
        randomStr += chars[randomIndex];
    }
    
    // 组合生成最终服务单号
    return `${uploaderId}-${dateStr}-${timestamp}-${randomStr}`;
}

// 数据库唯一性校验辅助函数
async function generateUniqueServiceOrderId(uploaderId, connection) {
    let orderId;
    let isDuplicate = true;
    
    // 最多尝试3次生成
    for (let i = 0; i < 3 && isDuplicate; i++) {
        orderId = generateServiceOrderId(uploaderId);
        
        // 检查数据库中是否已存在该单号
        const [existingOrders] = await connection.execute(
            'SELECT id FROM after_sales_order WHERE service_order_id = ?',
            [orderId]
        );
        
        isDuplicate = existingOrders.length > 0;
    }
    
    if (isDuplicate) {
        throw new Error('连续生成服务单号失败，请稍后重试');
    }
    
    return orderId;
}

// 验证物料数据的辅助函数
function validateMaterials(materials) {
    if (!Array.isArray(materials)) {
        throw new Error("替换物料数据格式错误，应为数组");
    }
    
    const requiredFields = ['code']; // 物料编码为必填项
    const numericFields = ['money', 'number']; // 金额和数量应为数字
    
    materials.forEach((material, index) => {
        // 验证必填字段
        requiredFields.forEach(field => {
            if (!material[field] || material[field].toString().trim() === '') {
                throw new Error(`第${index + 1}条物料缺少必填项: ${field}`);
            }
        });
        
        // 验证数字字段
        numericFields.forEach(field => {
            if (material[field] !== undefined && material[field] !== null) {
                const value = Number(material[field]);
                if (isNaN(value) || value < 0) {
                    throw new Error(`第${index + 1}条物料的${field}必须是非负数字`);
                }
            }
        });
    });
    
    return true;
}

router.post("/uploadOrder", upload.fields([
    { name: 'problem_description_list', maxCount: 1 },
    { name: 'problem_solving_solution', maxCount: 1 }
]), async function(req, res) {
    const connection = await db.getConnection();
    let result = {};
    const uploadedFiles = req.files;
    let filesToDelete = [];

    try {
        // 1. 自动生成服务单号
        const serviceOrderId = await generateUniqueServiceOrderId(req.auth.user_id, connection);
        
        // 2. 获取表单数据
        const data = req.body;
        const { 
            problem_description_reference,
            problem_solution_reference,
            materials // 获取替换物料数据（前端以JSON字符串形式传递）
        } = data;

        // 3. 解析并验证替换物料数据
        let parsedMaterials = [];
        if (materials) {
            try {
                parsedMaterials = JSON.parse(materials);
                validateMaterials(parsedMaterials); // 验证物料数据有效性
            } catch (parseError) {
                throw new Error(`替换物料数据解析失败: ${parseError.message}`);
            }
        }

        // 4. 处理问题描述清单文件
        let problemDescriptionFile = null;
        if (uploadedFiles['problem_description_list']) {
            const file = uploadedFiles['problem_description_list'][0];
            problemDescriptionFile = file.filename;
            filesToDelete.push(file.path);
        } else if (problem_description_reference) {
            const [refDescOrder] = await connection.execute(
                'SELECT problem_description_list FROM after_sales_order WHERE service_order_id = ?',
                [problem_description_reference]
            );
            
            if (refDescOrder.length === 0) {
                throw new Error(`问题描述引用失败：单号【${problem_description_reference}】不存在`);
            }
            
            if (!refDescOrder[0].problem_description_list) {
                throw new Error(`问题描述引用失败：单号【${problem_description_reference}】未上传附件`);
            }
            
            problemDescriptionFile = refDescOrder[0].problem_description_list;
        }

        // 5. 处理问题解决方案附件
        let problemSolutionFile = null;
        if (uploadedFiles['problem_solving_solution']) {
            const file = uploadedFiles['problem_solving_solution'][0];
            problemSolutionFile = file.filename;
            filesToDelete.push(file.path);
        } else if (problem_solution_reference) {
            const [refSolOrder] = await connection.execute(
                'SELECT problem_solving_solution FROM after_sales_order WHERE service_order_id = ?',
                [problem_solution_reference]
            );
            
            if (refSolOrder.length === 0) {
                throw new Error(`解决方案引用失败：单号【${problem_solution_reference}】不存在`);
            }
            
            if (!refSolOrder[0].problem_solving_solution) {
                throw new Error(`解决方案引用失败：单号【${problem_solution_reference}】未上传附件`);
            }
            
            problemSolutionFile = refSolOrder[0].problem_solving_solution;
        }

        // 6. 整理最终数据
        const finalData = {
            service_order_id: serviceOrderId,
            service_time: data.service_time || null,
            sn: data.sn || null,
            sales_order_id: data.sales_order_id || null,
            after_sales_person_id: data.after_sales_person_id || null,
            money: data.money || null,
            time: data.time || null,
            problem_description_list: problemDescriptionFile,
            problem_solving_solution: problemSolutionFile,
            main_problem_content_html: data.main_problem_content_html || null,
            specific_solution_measures_html: data.specific_solution_measures_html || null,
            insert_user_id: req.auth.user_id || null,
            create_time: getTime()
        };
        
        // 验证必要参数
        if (!finalData.insert_user_id) {
            throw new Error("操作人ID不能为空");
        }
        
        // 7. 执行数据库操作
        await db.beginTransaction(connection);
        
        // 7.1 插入售后服务单主记录
        const [insertResult] = await connection.execute(
            `INSERT INTO after_sales_order (
                service_order_id, service_time, sn, 
                sales_order_id, after_sales_person_id, money, time,
                problem_description_list, problem_solving_solution, insert_user_id, 
                create_time, main_problem_content_html, specific_solution_measures_html
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                finalData.service_order_id, finalData.service_time, finalData.sn,
                finalData.sales_order_id, finalData.after_sales_person_id, 
                finalData.money, finalData.time,
                finalData.problem_description_list, finalData.problem_solving_solution, 
                finalData.insert_user_id, finalData.create_time, 
                finalData.main_problem_content_html, finalData.specific_solution_measures_html
            ]
        );
        
        const afterSaleOrderId = insertResult.insertId; // 获取新增的售后服务单ID
        
        // 7.2 插入需替换物料记录
        if (parsedMaterials.length > 0) {
            const materialValues = [];
            const materialParams = [];
            
            parsedMaterials.forEach(material => {
                materialValues.push('(?, ?, ?, ?, ?)');
                materialParams.push(
                    afterSaleOrderId,
                    material.code,
                    material.money !== undefined ? material.money : null,
                    material.number !== undefined ? material.number : null,
                    getTime()
                );
            });
            
            await connection.execute(
                `INSERT INTO need_material (
                    after_sale_order_id, code, money, number, create_time
                ) VALUES ${materialValues.join(', ')}`,
                materialParams
            );
        }
        
        // 8. 生成日志信息
        const logFields = [
            `服务单号: ${finalData.service_order_id}`,
            finalData.sales_order_id ? `销售订单号: ${finalData.sales_order_id}` : '',
            finalData.service_time ? `服务时间: ${finalData.service_time}` : '',
            finalData.sn ? `产品序列号: ${finalData.sn}` : '',
            finalData.after_sales_person_id ? `售后人员ID: ${finalData.after_sales_person_id}` : '',
            finalData.money ? `金额: ${finalData.money}` : '',
            finalData.time ? `工时: ${finalData.time}` : '',
            parsedMaterials.length > 0 ? `替换物料数量: ${parsedMaterials.length}` : ''
        ].filter(Boolean);
        
        // 文件相关日志
        const fileLogs = [];
        if (problem_description_reference) {
            fileLogs.push(`问题描述: 引用单号【${problem_description_reference}】`);
        } else if (finalData.problem_description_list) {
            fileLogs.push(`问题描述: 已上传`);
        }
        
        if (problem_solution_reference) {
            fileLogs.push(`解决方案: 引用单号【${problem_solution_reference}】`);
        } else if (finalData.problem_solving_solution) {
            fileLogs.push(`解决方案: 已上传`);
        }
        
        // 组合所有日志信息并输出到控制台
        const allLogs = [...logFields, ...fileLogs].join('; ');
        const logMessage = `用户ID: ${finalData.insert_user_id} 新增售后服务单，${allLogs}`;
        console.log('售后服务单创建日志:', logMessage);
        
        // 记录操作日志到数据库
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?);`,
            [finalData.insert_user_id, logMessage, getTime()]
        );
        
        await db.commit(connection);
        
        // 9. 返回成功结果
        result = {
            code: 200,
            msg: "上传成功",
            data: { 
                orderId: afterSaleOrderId,
                serviceOrderId: serviceOrderId,
                materials: parsedMaterials,
                materialCount: parsedMaterials.length
            }
        };
    } catch (error) {
        await db.rollback(connection);
        await deleteUploadedFiles(filesToDelete.length > 0 ? filesToDelete : uploadedFiles);
        console.error('售后服务单创建错误:', error.message);
        result = {
            code: 500,
            msg: error.message || "服务器内部错误",
            errorType: error.message.includes("不存在") ? "REFERENCE_not_found" : 
                       error.message.includes("未上传附件") ? "attachment_missing" :
                       error.message.includes("替换物料") ? "material_error" :
                       error.message.includes("服务单号") ? "order_id_error" : "unknown"
        };
    } finally {
        connection.release();
    }
    
    res.json(result);
});

router.post("/updateOrder", upload.fields([
    { name: 'problem_description_list', maxCount: 1 },
    { name: 'problem_solving_solution', maxCount: 1 }
]), async function(req, res) { 
    const connection = await db.getConnection();
    let result = {};
    const uploadedFiles = req.files;
    
    try {
        const data = { ...req.body };
        if (!req.auth.user_id) {
            result = { code: 400, msg: "没有更新用户ID，不允许更新" };
            await deleteUploadedFiles(uploadedFiles);
            return res.json(result);
        }
        
        // 处理上传文件
        const fileFields = ['problem_description_list', 'problem_solving_solution'];
        fileFields.forEach(field => {
            if (uploadedFiles?.[field]?.[0]) {
                data[field] = uploadedFiles[field][0].filename;
            }
        });
        
        // 设置字段默认值
        const fieldsWithDefaultNull = [
            'service_order_id', 'service_time', 'sn',
            'sales_order_id', 'after_sales_person_id', 'money', 'time', 
            'main_problem_content_html', 'specific_solution_measures_html', ...fileFields
        ];
        fieldsWithDefaultNull.forEach(field => {
            data[field] = data[field] || null;
        });
        
        // 查询原始数据
        const [originalRows] = await connection.execute(
            'SELECT * FROM after_sales_order WHERE id = ?',
            [data.id]
        );
        
        if (originalRows.length === 0) {
            result = { code: 404, msg: "售后服务单记录不存在" };
            await deleteUploadedFiles(uploadedFiles);
            return res.json(result);
        }
        const originalData = originalRows[0];
        
        // 字段检查列表
        const fieldsToCheck = [
            'service_time', 'after_sales_person_id',
            'money', 'time', 'main_problem_content_html',
            'specific_solution_measures_html', ...fileFields
        ];
        
        // 辅助函数：统一字段值格式后比较
        const isValueChanged = (field, oldValue, newValue) => {
            // 空值统一处理
            if ([null, undefined, ''].includes(oldValue) && [null, undefined, ''].includes(newValue)) {
                return false;
            }
            
            // 数值型字段处理
            if (['money', 'after_sales_person_id'].includes(field)) {
                const numOld = oldValue === null ? null : Number(oldValue);
                const numNew = newValue === null ? null : Number(newValue);
                return numOld !== numNew;
            }
            
            // 时间字段处理
            if (field === 'service_time') {
                const standardizeTime = (timeStr) => {
                    if (!timeStr) return null;
                    if (/^\d{4}-\d{2}-\d{2}$/.test(timeStr)) {
                        return `${timeStr} 00:00:00`;
                    }
                    if (/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/.test(timeStr)) {
                        return timeStr;
                    }
                    return timeStr;
                };
                
                const stdOld = standardizeTime(oldValue);
                const stdNew = standardizeTime(newValue);
                return stdOld !== stdNew;
            }
            
            // 其他字段处理
            return String(oldValue ?? '') !== String(newValue ?? '');
        };
        
        const changes = [];
        const updateFields = [];
        const updateValues = [];
        
        fieldsToCheck.forEach(field => {
            const newValue = data[field];
            const oldValue = originalData[field];
            
            if (isValueChanged(field, oldValue, newValue)) {
                changes.push({
                    field,
                    oldValue: oldValue ?? '空',
                    newValue: newValue ?? '空'
                });
                updateFields.push(`${field} = ?`);
                updateValues.push(newValue);
            }
        });
        
        // 处理物料更新（如果有）
        let materialChanges = false;
        if (data.materials) {
            try {
                const newMaterials = JSON.parse(data.materials);
                validateMaterials(newMaterials);
                
                // 查询现有物料
                const [existingMaterials] = await connection.execute(
                    'SELECT * FROM need_material WHERE after_sale_order_id = ?',
                    [data.id]
                );
                
                // 如果有变化，先删除旧物料再插入新物料
                if (JSON.stringify(existingMaterials.map(m => ({
                    name: m.name, 
                    code: m.code, 
                    money: m.money, 
                    number: m.number
                }))) !== JSON.stringify(newMaterials)) {
                    // 删除旧物料
                    await connection.execute(
                        'DELETE FROM need_material WHERE after_sale_order_id = ?',
                        [data.id]
                    );
                    
                    // 插入新物料
                    if (newMaterials.length > 0) {
                        const materialValues = [];
                        const materialParams = [];
                        
                        newMaterials.forEach(material => {
                            materialValues.push('(?, ?, ?, ?, ?, ?)');
                            materialParams.push(
                                data.id,
                                material.name,
                                material.code,
                                material.money !== undefined ? material.money : null,
                                material.number !== undefined ? material.number : null,
                                getTime()
                            );
                        });
                        
                        await connection.execute(
                            `INSERT INTO need_material (
                                after_sale_order_id, name, code, money, number, create_time
                            ) VALUES ${materialValues.join(', ')}`,
                            materialParams
                        );
                    }
                    
                    materialChanges = true;
                    changes.push({
                        field: 'materials',
                        oldValue: `原有${existingMaterials.length}条物料`,
                        newValue: `更新为${newMaterials.length}条物料`
                    });
                }
            } catch (parseError) {
                throw new Error(`替换物料数据更新失败: ${parseError.message}`);
            }
        }
        
        if (changes.length === 0) {
            result = { code: 200, msg: "没有需要更新的内容" };
            await deleteUploadedFiles(uploadedFiles);
            return res.json(result);
        }
        
        // 执行更新
        updateFields.push('update_time = ?', 'update_user_id = ?');
        updateValues.push(getTime(), req.auth.user_id, data.id);
        
        await db.beginTransaction(connection);
        await connection.execute(
            `UPDATE after_sales_order SET ${updateFields.join(', ')} WHERE id = ?`,
            updateValues
        );
        
        // 记录日志
        const changeDescription = changes.map(change => 
            `${change.field}: ${change.oldValue} → ${change.newValue}`
        ).join('; ');
        
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
            [req.auth.user_id, `用户ID: ${req.auth.user_id} 更新了售后服务单ID: ${data.id}，变更内容: ${changeDescription}`, getTime()]
        );
        
        await db.commit(connection);
        
        result = {
            code: 200,
            msg: "更新成功",
            changes: changes
        };
    } catch (error) {
        await db.rollback(connection);
        await deleteUploadedFiles(uploadedFiles);
        result = {
            code: 500,
            msg: error.message || "服务器内部错误"
        };
    } finally {
        connection.release();
    }
    
    res.json(result);
});

router.post('/searchAfterSales', async function(req, res) { 
    var result = {};
    var data = req.body;
    var sql = "SELECT * FROM after_sales_order WHERE 1=1";
    var getTotalSql = "SELECT COUNT(*) AS total FROM after_sales_order WHERE 1=1";
    var params = [];
    
    try {
        for (const key in data.condition) {
            if (data.condition[key] != null) {
                sql += ` AND ${key}=?`;
                getTotalSql += ` AND ${key}=?`;
                params.push(data.condition[key]);
            }
        }
        
        const [totalResult] = await db.query(getTotalSql, params);
        const total = totalResult.total;
        
        sql += " LIMIT ? OFFSET ?";
        params.push(data.pageSize);
        params.push(data.offset);
        
        const afterSalesList = await db.query(sql, params);
        
        // 查询每个订单的物料信息
        for (const order of afterSalesList) {
            const [materials] = await db.query(
                'SELECT * FROM need_material WHERE after_sale_order_id = ?',
                [order.id]
            );
            order.materials = materials;
        }
        
        result = {
            data: afterSalesList,
            code: 200,
            msg: "查询成功~",
            total: total
        };
    } catch (error) {
        console.log(error)
        result = {
            code: 500,
            msg: "查询失败"
        };
    }
    
    res.send(JSON.stringify(result));
});

router.post('/getAfterSalesInfo', async function(req, res) { 
    var result = {};
    var data = req.body;
    
    try {
        const afterSale = await db.query(
            "SELECT * FROM after_sales_order WHERE id=?",
            [data.id]
        );
        
        // 获取关联的物料信息
        if (afterSale.length > 0) {
            const [materials] = await db.query(
                'SELECT * FROM need_material WHERE after_sale_order_id = ?',
                [data.id]
            );
            afterSale[0].materials = materials;
        }
        
        result = {
            data: afterSale[0],
            code: 200,
            msg: "查询成功~"
        };
    } catch (error) {
        result = {
            code: 500,
            msg: "查询失败"
        };
    }
    
    res.send(JSON.stringify(result));
});

// 错误处理中间件
router.use((err, _req, res, next) => {
    if (err instanceof multer.MulterError) {
        console.error("Multer文件上传错误:", err);
        return res.status(500).json({ 
            code: 500, 
            msg: `文件上传错误: ${err.message}`
        });
    }
    next(err);
});

module.exports = router;
    